using System;
using System.Collections.Generic;
using System.Text;
using DTO;
using System.Data;
using System.Data.SqlClient;

namespace DAO
{
    /// <summary> 
    ///Author: nnamthach@gmail.com 
    /// <summary>
    public class ContractDataAccess : Connection
    {
        #region Local Variable
        private string _strSPInsertName = "dbo.[procContract_add]";
        private string _strSPUpdateName = "dbo.[procContract_update]";
        private string _strSPDeleteName = "dbo.[procContract_delete]";
        private string _strSPGetName = "dbo.[procContract_get]";
        private string _strSPGetAllName = "dbo.[procContract_getall]";
        private string _strSPGetPages = "dbo.[procContract_getpaged]";
        private string _strSPIsExist = "dbo.[procContract_isexist]";
        private string _strTableName = "Contract";

        private string _strSPInsertName1 = "dbo.[procContract1_add]";
        private string _strSPUpdateName1 = "dbo.[procContract1_update]";
        private string _strSPDeleteName1 = "dbo.[procContract1_delete]";
        private string _strSPGetName1 = "dbo.[procContract1_get]";
        private string _strSPGetAllName1 = "dbo.[procContract1_getall]";
        private string _strSPGetPages1 = "dbo.[procContract1_getpaged]";
        private string _strSPIsExist1 = "dbo.[procContract1_isexist]";


        private string _strSPGetCondName = "dbo.[procContract_getCond]";
        private string _strSPGetCondName1 = "dbo.[procContract1_getCond]";

        #endregion Local Variable

        #region Method
        public ContractInfo Get_Contract(
            String Contract
        , ref string sErr)
        {
            ContractInfo objEntr = new ContractInfo();
            InitConnect();
            InitSPCommand(_strSPGetName);
            AddParameter("Contract", Contract);

            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }

            if (list.Rows.Count > 0)
                objEntr = (ContractInfo)GetDataFromDataRow(list, 0);
            //if (dr != null) list = CBO.FillCollection(dr, ref list);
            if (sErr != "") ErrorLog.SetLog(sErr);
            return objEntr;
        }

        public ContractInfo Get_Contract1(
           String Contract
       , ref string sErr)
        {
            ContractInfo objEntr = new ContractInfo();
            InitConnect();
            InitSPCommand(_strSPGetName1);
            AddParameter("Contract", Contract);

            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }

            if (list.Rows.Count > 0)
                objEntr = (ContractInfo)GetDataFromDataRow(list, 0);
            //if (dr != null) list = CBO.FillCollection(dr, ref list);
            if (sErr != "") ErrorLog.SetLog(sErr);
            return objEntr;
        }


        protected override object GetDataFromDataRow(DataTable dt, int i)
        {
            ContractInfo result = new ContractInfo();
            result.Contract = (dt.Rows[i]["Contract"] == DBNull.Value ? "" : (String)dt.Rows[i]["Contract"]);
            result.LimitAmt = (dt.Rows[i]["LimitAmt"] == DBNull.Value ? 0 : (Double)dt.Rows[i]["LimitAmt"]);
            result.CIT = (dt.Rows[i]["CIT"] == DBNull.Value ? 0 : (Double)dt.Rows[i]["CIT"]);
            result.VAT = (dt.Rows[i]["VAT"] == DBNull.Value ? 0 : (Double)dt.Rows[i]["VAT"]);
            result.CummulativeAmt = (dt.Rows[i]["CummulativeAmt"] == DBNull.Value ? 0 : (Double)dt.Rows[i]["CummulativeAmt"]);
            result.RemainingAmt = (dt.Rows[i]["RemainingAmt"] == DBNull.Value ? 0 : (Double)dt.Rows[i]["RemainingAmt"]);
            result.ContractHolder = (dt.Rows[i]["ContractHolder"] == DBNull.Value ? "" : (String)dt.Rows[i]["ContractHolder"]);
            result.Desc = (dt.Rows[i]["Desc"] == DBNull.Value ? "" : (String)dt.Rows[i]["Desc"]);
            result.Department = (dt.Rows[i]["Department"] == DBNull.Value ? "" : (String)dt.Rows[i]["Department"]);
            result.SharedContract = (dt.Rows[i]["SharedContract"] == DBNull.Value ? -1 : (Int32)dt.Rows[i]["SharedContract"]);
            result.ActualCost = (dt.Rows[i]["ActualCost"] == DBNull.Value ? 0 : (Double)dt.Rows[i]["ActualCost"]);
            result.SignDate = (dt.Rows[i]["SignDate"] == DBNull.Value ? DateTime.Now : (DateTime)dt.Rows[i]["SignDate"]);
            result.EffectiveDate = (dt.Rows[i]["EffectiveDate"] == DBNull.Value ? DateTime.Now : (DateTime)dt.Rows[i]["EffectiveDate"]);
            result.ExpiryDate = (dt.Rows[i]["ExpiryDate"] == DBNull.Value ? DateTime.Now : (DateTime)dt.Rows[i]["ExpiryDate"]);
            result.PaymentTerm = (dt.Rows[i]["PaymentTerm"] == DBNull.Value ? (Int16)0 : (Int16)dt.Rows[i]["PaymentTerm"]);
            result.Remark1 = (dt.Rows[i]["Remark1"] == DBNull.Value ? "" : (String)dt.Rows[i]["Remark1"]);
            result.Remark2 = (dt.Rows[i]["Remark2"] == DBNull.Value ? "" : (String)dt.Rows[i]["Remark2"]);
            result.Remark3 = (dt.Rows[i]["Remark3"] == DBNull.Value ? "" : (String)dt.Rows[i]["Remark3"]);
            result.MCApproval = (dt.Rows[i]["MCApproval"] == DBNull.Value ? -1 : (Int32)dt.Rows[i]["MCApproval"]);
            result.ActionRequired = (dt.Rows[i]["ActionRequired"] == DBNull.Value ? "" : (String)dt.Rows[i]["ActionRequired"]);
            result.Status = (dt.Rows[i]["Status"] == DBNull.Value ? "" : (String)dt.Rows[i]["Status"]);
            result.BudgetCode = (dt.Rows[i]["BudgetCode"] == DBNull.Value ? "" : (String)dt.Rows[i]["BudgetCode"]);
            result.BudgetYear = (dt.Rows[i]["BudgetYear"] == DBNull.Value ? -1 : (Int32)dt.Rows[i]["BudgetYear"]);
            result.YearBudgetCode = (dt.Rows[i]["YearBudgetCode"] == DBNull.Value ? "" : (String)dt.Rows[i]["YearBudgetCode"]);
            result.VendorCode = (dt.Rows[i]["VendorCode"] == DBNull.Value ? "" : (String)dt.Rows[i]["VendorCode"]);
            result.VendorName = (dt.Rows[i]["VendorName"] == DBNull.Value ? "" : (String)dt.Rows[i]["VendorName"]);
            result.Cur = (dt.Rows[i]["Cur"] == DBNull.Value ? "" : (String)dt.Rows[i]["Cur"]);
            result.UserRef = (dt.Rows[i]["UserRef"] == DBNull.Value ? "" : (String)dt.Rows[i]["UserRef"]);
            result.ContractType = (dt.Rows[i]["ContractType"] == DBNull.Value ? "" : (String)dt.Rows[i]["ContractType"]);
            result.isLink = (dt.Rows[i]["isLink"] == DBNull.Value ? (Int16)0 : (Int16)dt.Rows[i]["isLink"]);
            result.aType = (dt.Rows[i]["aType"] == DBNull.Value ? "" : (String)dt.Rows[i]["aType"]);

            return result;
        }

        public DataTable GetAll_Contract(ref string sErr)
        {
            InitConnect();
            InitCommand(@"SELECT     Contract, LimitAmt, CIT, VAT, CummulativeAmt, RemainingAmt, ContractHolder, [Desc], Department, SharedContract, ActualCost, SignDate, EffectiveDate, ExpiryDate, 
                      PaymentTerm, Remark1, Remark2, Remark3, MCApproval, ActionRequired, Status, BudgetCode, BudgetYear, YearBudgetCode, VendorCode, VendorName, Cur, 
                      UserRef, ContractType, isLink, aType, Contract +' | ' + isnull([Desc],'') as Code
                FROM         Contract", CommandType.Text);

            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }

            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }

        public DataTable GetAll_Contract1(ref string sErr)
        {
            InitConnect();
            InitCommand(@"SELECT     Contract, LimitAmt, CIT, VAT, CummulativeAmt, RemainingAmt, ContractHolder, [Desc], Department, SharedContract, ActualCost, SignDate, EffectiveDate, ExpiryDate, 
                      PaymentTerm, Remark1, Remark2, Remark3, MCApproval, ActionRequired, Status, BudgetCode, BudgetYear, YearBudgetCode, VendorCode, VendorName, Cur, 
                      UserRef, ContractType, isLink, aType, Contract +' | ' + isnull([Desc],'') as Code
                FROM         Contract1", CommandType.Text);

            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }

            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
        /// <summary>
        /// Return 1: Table is exist Identity Field
        /// Return 0: Table is not exist Identity Field
        /// Return -1: Erro
        /// </summary>
        /// <param name="tableName"></param>
        public Int32 Add_Contract(ContractInfo objEntr, ref string sErr)
        {
            int ret = -1;
            openConnection();
            InitSPCommand(_strSPInsertName);
            AddParameter("Contract", objEntr.Contract);
            AddParameter("LimitAmt", objEntr.LimitAmt);
            //AddParameter("CIT", objEntr.CIT);
            if (objEntr.CIT == 0)
                AddParameter("CIT", DBNull.Value);
            else
                AddParameter("CIT", objEntr.CIT);
            //AddParameter("VAT", objEntr.VAT);
            if (objEntr.VAT == 0)
                AddParameter("VAT", DBNull.Value);
            else
                AddParameter("VAT", objEntr.VAT);

            AddParameter("CummulativeAmt", objEntr.CummulativeAmt);
            AddParameter("RemainingAmt", objEntr.RemainingAmt);
            AddParameter("ContractHolder", objEntr.ContractHolder);
            AddParameter("Desc", objEntr.Desc);
            AddParameter("Department", objEntr.Department);
            AddParameter("SharedContract", objEntr.SharedContract);
            //AddParameter("ActualCost", objEntr.ActualCost);
            if (objEntr.ActualCost == 0)
                AddParameter("ActualCost", DBNull.Value);
            else
                AddParameter("ActualCost", objEntr.ActualCost);

            //AddParameter("SignDate", objEntr.SignDate);
            if (objEntr.SignDate == DateTime.MinValue)
                AddParameter("SignDate", DBNull.Value);
            else
                AddParameter("SignDate", objEntr.SignDate);
            //AddParameter("EffectiveDate", objEntr.EffectiveDate);
            if (objEntr.EffectiveDate == DateTime.MinValue)
                AddParameter("EffectiveDate", DBNull.Value);
            else
                AddParameter("EffectiveDate", objEntr.EffectiveDate);
            //AddParameter("ExpiryDate", objEntr.ExpiryDate);
            if (objEntr.ExpiryDate == DateTime.MinValue)
                AddParameter("ExpiryDate", DBNull.Value);
            else
                AddParameter("ExpiryDate", objEntr.ExpiryDate);

            //AddParameter("PaymentTerm", objEntr.PaymentTerm);
            if (objEntr.PaymentTerm == 0)
                AddParameter("PaymentTerm", DBNull.Value);
            else
                AddParameter("PaymentTerm", objEntr.PaymentTerm);

            //AddParameter("Remark1", objEntr.Remark1);
            if (objEntr.Remark1 == "")
                AddParameter("Remark1", DBNull.Value);
            else
                AddParameter("Remark1", objEntr.Remark1);

            //AddParameter("Remark2", objEntr.Remark2);
            if (objEntr.Remark2 == "")
                AddParameter("Remark2", DBNull.Value);
            else
                AddParameter("Remark2", objEntr.Remark2);

            //AddParameter("Remark3", objEntr.Remark3);
            if (objEntr.Remark3 == "")
                AddParameter("Remark3", DBNull.Value);
            else
                AddParameter("Remark3", objEntr.Remark3);

            //AddParameter("MCApproval", objEntr.MCApproval);
            if (objEntr.MCApproval == -1)
                AddParameter("MCApproval", DBNull.Value);
            else
                AddParameter("MCApproval", objEntr.MCApproval);

            //AddParameter("ActionRequired", objEntr.ActionRequired);
            if (objEntr.ActionRequired == "")
                AddParameter("ActionRequired", DBNull.Value);
            else
                AddParameter("ActionRequired", objEntr.ActionRequired);

            //AddParameter("Status", objEntr.Status);
            if (objEntr.Status == "")
                AddParameter("Status", DBNull.Value);
            else
                AddParameter("Status", objEntr.Status);

            //AddParameter("BudgetCode", objEntr.BudgetCode);
            if (objEntr.BudgetCode == "")
                AddParameter("BudgetCode", DBNull.Value);
            else
                AddParameter("BudgetCode", objEntr.BudgetCode);

            //AddParameter("BudgetYear", objEntr.BudgetYear);
            if (objEntr.BudgetYear == -1)
                AddParameter("BudgetYear", DBNull.Value);
            else
                AddParameter("BudgetYear", objEntr.BudgetYear);

            //AddParameter("YearBudgetCode", objEntr.YearBudgetCode);
            if (objEntr.YearBudgetCode == "")
                AddParameter("YearBudgetCode", DBNull.Value);
            else
                AddParameter("YearBudgetCode", objEntr.YearBudgetCode);


            AddParameter("VendorCode", objEntr.VendorCode);
            AddParameter("VendorName", objEntr.VendorName);
            //AddParameter("Cur", objEntr.Cur);
            if (objEntr.Cur == "")
                AddParameter("Cur", DBNull.Value);
            else
                AddParameter("Cur", objEntr.Cur);

            //AddParameter("UserRef", objEntr.UserRef);
            if (objEntr.UserRef == "")
                AddParameter("UserRef", DBNull.Value);
            else
                AddParameter("UserRef", objEntr.UserRef);

            AddParameter("ContractType", objEntr.ContractType);
            AddParameter("isLink", objEntr.isLink);
            AddParameter("aType", objEntr.aType);

            try
            {
                //command.ExecuteNonQuery();
                object tmp = executeScalar();
                if (tmp != null && tmp != DBNull.Value)
                    ret = Convert.ToInt32(tmp);
                else
                    ret = 0;
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }
            if (sErr != "") ErrorLog.SetLog(sErr);

            return ret;
        }
        public Int32 Add_Contract1(ContractInfo objEntr, ref string sErr)
        {
            int ret = -1;
            openConnection();
            InitSPCommand(_strSPInsertName1);
            AddParameter("Contract", objEntr.Contract);
            AddParameter("LimitAmt", objEntr.LimitAmt);
            //AddParameter("CIT", objEntr.CIT);
            if (objEntr.CIT == 0)
                AddParameter("CIT", DBNull.Value);
            else
                AddParameter("CIT", objEntr.CIT);
            //AddParameter("VAT", objEntr.VAT);
            if (objEntr.VAT == 0)
                AddParameter("VAT", DBNull.Value);
            else
                AddParameter("VAT", objEntr.VAT);

            AddParameter("CummulativeAmt", objEntr.CummulativeAmt);
            AddParameter("RemainingAmt", objEntr.RemainingAmt);
            AddParameter("ContractHolder", objEntr.ContractHolder);
            AddParameter("Desc", objEntr.Desc);
            AddParameter("Department", objEntr.Department);
            AddParameter("SharedContract", objEntr.SharedContract);
            //AddParameter("ActualCost", objEntr.ActualCost);
            if (objEntr.ActualCost == 0)
                AddParameter("ActualCost", DBNull.Value);
            else
                AddParameter("ActualCost", objEntr.ActualCost);

            //AddParameter("SignDate", objEntr.SignDate);
            if (objEntr.SignDate == DateTime.MinValue)
                AddParameter("SignDate", DBNull.Value);
            else
                AddParameter("SignDate", objEntr.SignDate);
            //AddParameter("EffectiveDate", objEntr.EffectiveDate);
            if (objEntr.EffectiveDate == DateTime.MinValue)
                AddParameter("EffectiveDate", DBNull.Value);
            else
                AddParameter("EffectiveDate", objEntr.EffectiveDate);
            //AddParameter("ExpiryDate", objEntr.ExpiryDate);
            if (objEntr.ExpiryDate == DateTime.MinValue)
                AddParameter("ExpiryDate", DBNull.Value);
            else
                AddParameter("ExpiryDate", objEntr.ExpiryDate);

            //AddParameter("PaymentTerm", objEntr.PaymentTerm);
            if (objEntr.PaymentTerm == 0)
                AddParameter("PaymentTerm", DBNull.Value);
            else
                AddParameter("PaymentTerm", objEntr.PaymentTerm);

            //AddParameter("Remark1", objEntr.Remark1);
            if (objEntr.Remark1 == "")
                AddParameter("Remark1", DBNull.Value);
            else
                AddParameter("Remark1", objEntr.Remark1);

            //AddParameter("Remark2", objEntr.Remark2);
            if (objEntr.Remark2 == "")
                AddParameter("Remark2", DBNull.Value);
            else
                AddParameter("Remark2", objEntr.Remark2);

            //AddParameter("Remark3", objEntr.Remark3);
            if (objEntr.Remark3 == "")
                AddParameter("Remark3", DBNull.Value);
            else
                AddParameter("Remark3", objEntr.Remark3);

            //AddParameter("MCApproval", objEntr.MCApproval);
            if (objEntr.MCApproval == -1)
                AddParameter("MCApproval", DBNull.Value);
            else
                AddParameter("MCApproval", objEntr.MCApproval);

            //AddParameter("ActionRequired", objEntr.ActionRequired);
            if (objEntr.ActionRequired == "")
                AddParameter("ActionRequired", DBNull.Value);
            else
                AddParameter("ActionRequired", objEntr.ActionRequired);

            //AddParameter("Status", objEntr.Status);
            if (objEntr.Status == "")
                AddParameter("Status", DBNull.Value);
            else
                AddParameter("Status", objEntr.Status);

            //AddParameter("BudgetCode", objEntr.BudgetCode);
            if (objEntr.BudgetCode == "")
                AddParameter("BudgetCode", DBNull.Value);
            else
                AddParameter("BudgetCode", objEntr.BudgetCode);

            //AddParameter("BudgetYear", objEntr.BudgetYear);
            if (objEntr.BudgetYear == -1)
                AddParameter("BudgetYear", DBNull.Value);
            else
                AddParameter("BudgetYear", objEntr.BudgetYear);

            //AddParameter("YearBudgetCode", objEntr.YearBudgetCode);
            if (objEntr.YearBudgetCode == "")
                AddParameter("YearBudgetCode", DBNull.Value);
            else
                AddParameter("YearBudgetCode", objEntr.YearBudgetCode);


            AddParameter("VendorCode", objEntr.VendorCode);
            AddParameter("VendorName", objEntr.VendorName);
            //AddParameter("Cur", objEntr.Cur);
            if (objEntr.Cur == "")
                AddParameter("Cur", DBNull.Value);
            else
                AddParameter("Cur", objEntr.Cur);

            //AddParameter("UserRef", objEntr.UserRef);
            if (objEntr.UserRef == "")
                AddParameter("UserRef", DBNull.Value);
            else
                AddParameter("UserRef", objEntr.UserRef);

            AddParameter("ContractType", objEntr.ContractType);
            AddParameter("isLink", objEntr.isLink);
            AddParameter("aType", objEntr.aType);

            try
            {
                //command.ExecuteNonQuery();
                object tmp = executeScalar();
                if (tmp != null && tmp != DBNull.Value)
                    ret = Convert.ToInt32(tmp);
                else
                    ret = 0;
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }
            if (sErr != "") ErrorLog.SetLog(sErr);

            return ret;
        }


        public DataTable GetCond(string strCond, ref string sErr)
        {
            InitConnect();
            InitSPCommand(_strSPGetCondName);
            AddParameter("Cond", strCond);
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
        public DataTable GetCond1(string strCond, ref string sErr)
        {
            InitConnect();
            InitSPCommand(_strSPGetCondName1);
            AddParameter("Cond", strCond);
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }

        public string Update_Contract(ContractInfo objEntr)
        {

            openConnection();
            InitSPCommand(_strSPUpdateName);

            AddParameter("Contract", objEntr.Contract);
            AddParameter("LimitAmt", objEntr.LimitAmt);
            //AddParameter("CIT", objEntr.CIT);
            if (objEntr.CIT == 0)
                AddParameter("CIT", DBNull.Value);
            else
                AddParameter("CIT", objEntr.CIT);
            //AddParameter("VAT", objEntr.VAT);
            if (objEntr.VAT == 0)
                AddParameter("VAT", DBNull.Value);
            else
                AddParameter("VAT", objEntr.VAT);

            AddParameter("CummulativeAmt", objEntr.CummulativeAmt);
            AddParameter("RemainingAmt", objEntr.RemainingAmt);
            AddParameter("ContractHolder", objEntr.ContractHolder);
            AddParameter("Desc", objEntr.Desc);
            AddParameter("Department", objEntr.Department);
            AddParameter("SharedContract", objEntr.SharedContract);
            //AddParameter("ActualCost", objEntr.ActualCost);
            if (objEntr.ActualCost == 0)
                AddParameter("ActualCost", DBNull.Value);
            else
                AddParameter("ActualCost", objEntr.ActualCost);

            //AddParameter("SignDate", objEntr.SignDate);
            if (objEntr.SignDate == DateTime.MinValue)
                AddParameter("SignDate", DBNull.Value);
            else
                AddParameter("SignDate", objEntr.SignDate);
            //AddParameter("EffectiveDate", objEntr.EffectiveDate);
            if (objEntr.EffectiveDate == DateTime.MinValue)
                AddParameter("EffectiveDate", DBNull.Value);
            else
                AddParameter("EffectiveDate", objEntr.EffectiveDate);
            //AddParameter("ExpiryDate", objEntr.ExpiryDate);
            if (objEntr.ExpiryDate == DateTime.MinValue)
                AddParameter("ExpiryDate", DBNull.Value);
            else
                AddParameter("ExpiryDate", objEntr.ExpiryDate);

            AddParameter("PaymentTerm", objEntr.PaymentTerm);

            //AddParameter("Remark1", objEntr.Remark1);
            if (objEntr.Remark1 == "")
                AddParameter("Remark1", DBNull.Value);
            else
                AddParameter("Remark1", objEntr.Remark1);

            //AddParameter("Remark2", objEntr.Remark2);
            if (objEntr.Remark2 == "")
                AddParameter("Remark2", DBNull.Value);
            else
                AddParameter("Remark2", objEntr.Remark2);

            //AddParameter("Remark3", objEntr.Remark3);
            if (objEntr.Remark3 == "")
                AddParameter("Remark3", DBNull.Value);
            else
                AddParameter("Remark3", objEntr.Remark3);

            //AddParameter("MCApproval", objEntr.MCApproval);
            if (objEntr.MCApproval == -1)
                AddParameter("MCApproval", DBNull.Value);
            else
                AddParameter("MCApproval", objEntr.MCApproval);

            //AddParameter("ActionRequired", objEntr.ActionRequired);
            if (objEntr.ActionRequired == "")
                AddParameter("ActionRequired", DBNull.Value);
            else
                AddParameter("ActionRequired", objEntr.ActionRequired);

            //AddParameter("Status", objEntr.Status);
            if (objEntr.Status == "")
                AddParameter("Status", DBNull.Value);
            else
                AddParameter("Status", objEntr.Status);

            //AddParameter("BudgetCode", objEntr.BudgetCode);
            if (objEntr.BudgetCode == "")
                AddParameter("BudgetCode", DBNull.Value);
            else
                AddParameter("BudgetCode", objEntr.BudgetCode);

            //AddParameter("BudgetYear", objEntr.BudgetYear);
            if (objEntr.BudgetYear == -1)
                AddParameter("BudgetYear", DBNull.Value);
            else
                AddParameter("BudgetYear", objEntr.BudgetYear);

            //AddParameter("YearBudgetCode", objEntr.YearBudgetCode);
            if (objEntr.YearBudgetCode == "")
                AddParameter("YearBudgetCode", DBNull.Value);
            else
                AddParameter("YearBudgetCode", objEntr.YearBudgetCode);


            AddParameter("VendorCode", objEntr.VendorCode);
            AddParameter("VendorName", objEntr.VendorName);
            //AddParameter("Cur", objEntr.Cur);
            if (objEntr.Cur == "")
                AddParameter("Cur", DBNull.Value);
            else
                AddParameter("Cur", objEntr.Cur);

            //AddParameter("UserRef", objEntr.UserRef);
            if (objEntr.UserRef == "")
                AddParameter("UserRef", DBNull.Value);
            else
                AddParameter("UserRef", objEntr.UserRef);

            AddParameter("ContractType", objEntr.ContractType);
            AddParameter("isLink", objEntr.isLink);
            AddParameter("aType", objEntr.aType);

            string sErr = "";
            try
            {
                executeNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }
            if (sErr != "") ErrorLog.SetLog(sErr);
            return sErr;
        }
        public string Update_Contract1(ContractInfo objEntr)
        {

            openConnection();
            InitSPCommand(_strSPUpdateName1);

            AddParameter("Contract", objEntr.Contract);
            AddParameter("LimitAmt", objEntr.LimitAmt);
            //AddParameter("CIT", objEntr.CIT);
            if (objEntr.CIT == 0)
                AddParameter("CIT", DBNull.Value);
            else
                AddParameter("CIT", objEntr.CIT);
            //AddParameter("VAT", objEntr.VAT);
            if (objEntr.VAT == 0)
                AddParameter("VAT", DBNull.Value);
            else
                AddParameter("VAT", objEntr.VAT);

            AddParameter("CummulativeAmt", objEntr.CummulativeAmt);
            AddParameter("RemainingAmt", objEntr.RemainingAmt);
            AddParameter("ContractHolder", objEntr.ContractHolder);
            AddParameter("Desc", objEntr.Desc);
            AddParameter("Department", objEntr.Department);
            AddParameter("SharedContract", objEntr.SharedContract);
            //AddParameter("ActualCost", objEntr.ActualCost);
            if (objEntr.ActualCost == 0)
                AddParameter("ActualCost", DBNull.Value);
            else
                AddParameter("ActualCost", objEntr.ActualCost);

            //AddParameter("SignDate", objEntr.SignDate);
            if (objEntr.SignDate == DateTime.MinValue)
                AddParameter("SignDate", DBNull.Value);
            else
                AddParameter("SignDate", objEntr.SignDate);
            //AddParameter("EffectiveDate", objEntr.EffectiveDate);
            if (objEntr.EffectiveDate == DateTime.MinValue)
                AddParameter("EffectiveDate", DBNull.Value);
            else
                AddParameter("EffectiveDate", objEntr.EffectiveDate);
            //AddParameter("ExpiryDate", objEntr.ExpiryDate);
            if (objEntr.ExpiryDate == DateTime.MinValue)
                AddParameter("ExpiryDate", DBNull.Value);
            else
                AddParameter("ExpiryDate", objEntr.ExpiryDate);

            AddParameter("PaymentTerm", objEntr.PaymentTerm);

            //AddParameter("Remark1", objEntr.Remark1);
            if (objEntr.Remark1 == "")
                AddParameter("Remark1", DBNull.Value);
            else
                AddParameter("Remark1", objEntr.Remark1);

            //AddParameter("Remark2", objEntr.Remark2);
            if (objEntr.Remark2 == "")
                AddParameter("Remark2", DBNull.Value);
            else
                AddParameter("Remark2", objEntr.Remark2);

            //AddParameter("Remark3", objEntr.Remark3);
            if (objEntr.Remark3 == "")
                AddParameter("Remark3", DBNull.Value);
            else
                AddParameter("Remark3", objEntr.Remark3);

            //AddParameter("MCApproval", objEntr.MCApproval);
            if (objEntr.MCApproval == -1)
                AddParameter("MCApproval", DBNull.Value);
            else
                AddParameter("MCApproval", objEntr.MCApproval);

            //AddParameter("ActionRequired", objEntr.ActionRequired);
            if (objEntr.ActionRequired == "")
                AddParameter("ActionRequired", DBNull.Value);
            else
                AddParameter("ActionRequired", objEntr.ActionRequired);

            //AddParameter("Status", objEntr.Status);
            if (objEntr.Status == "")
                AddParameter("Status", DBNull.Value);
            else
                AddParameter("Status", objEntr.Status);

            //AddParameter("BudgetCode", objEntr.BudgetCode);
            if (objEntr.BudgetCode == "")
                AddParameter("BudgetCode", DBNull.Value);
            else
                AddParameter("BudgetCode", objEntr.BudgetCode);

            //AddParameter("BudgetYear", objEntr.BudgetYear);
            if (objEntr.BudgetYear == -1)
                AddParameter("BudgetYear", DBNull.Value);
            else
                AddParameter("BudgetYear", objEntr.BudgetYear);

            //AddParameter("YearBudgetCode", objEntr.YearBudgetCode);
            if (objEntr.YearBudgetCode == "")
                AddParameter("YearBudgetCode", DBNull.Value);
            else
                AddParameter("YearBudgetCode", objEntr.YearBudgetCode);


            AddParameter("VendorCode", objEntr.VendorCode);
            AddParameter("VendorName", objEntr.VendorName);
            //AddParameter("Cur", objEntr.Cur);
            if (objEntr.Cur == "")
                AddParameter("Cur", DBNull.Value);
            else
                AddParameter("Cur", objEntr.Cur);

            //AddParameter("UserRef", objEntr.UserRef);
            if (objEntr.UserRef == "")
                AddParameter("UserRef", DBNull.Value);
            else
                AddParameter("UserRef", objEntr.UserRef);

            AddParameter("ContractType", objEntr.ContractType);
            AddParameter("isLink", objEntr.isLink);
            AddParameter("aType", objEntr.aType);

            string sErr = "";
            try
            {
                executeNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }
            if (sErr != "") ErrorLog.SetLog(sErr);
            return sErr;
        }

        public string Delete_Contract(
            String Contract
        )
        {
            openConnection();
            InitSPCommand(_strSPDeleteName);
            AddParameter("Contract", Contract);

            string sErr = "";
            try
            {
                executeNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }
            if (sErr != "") ErrorLog.SetLog(sErr);
            return sErr;
        }

        public string Delete_Contract1(
          String Contract
      )
        {
            openConnection();
            InitSPCommand(_strSPDeleteName1);
            AddParameter("Contract", Contract);

            string sErr = "";
            try
            {
                executeNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }
            if (sErr != "") ErrorLog.SetLog(sErr);
            return sErr;
        }

        public Boolean IsExist_Contract(
            String Contract
        )
        {
            InitConnect();
            InitSPCommand(_strSPIsExist);

            AddParameter("Contract", Contract);

            string sErr = "";
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }
            if (sErr != "") ErrorLog.SetLog(sErr);
            if (list.Rows.Count == 1)
                return true;
            return false;
        }

        public Boolean IsExist_Contract1(
            String Contract
        )
        {
            InitConnect();
            InitSPCommand(_strSPIsExist1);

            AddParameter("Contract", Contract);

            string sErr = "";
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }
            if (sErr != "") ErrorLog.SetLog(sErr);
            if (list.Rows.Count == 1)
                return true;
            return false;
        }

        private string CreateWhereClause(ContractInfo obj)
        {
            String result = "";

            return result;
        }

        public DataTable Search(string columnName, string columnValue, string condition, ref string sErr)
        {
            string query = "select * from " + _strTableName + " where " + columnName + " " + condition + " " + columnValue;
            DataTable list = new DataTable();
            openConnection();
            try
            {
                list = executeSelectQuery(query);
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }
            //if (dr != null) list = CBO.FillCollection(dr, ref list);
            //    if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
        #endregion Method


        public decimal getTotalAmtHL(string code, ref string sErr)
        {
            decimal ret = 0;
            openConnection();
            string sql = @"SELECT isnull(SUM(LimitAmt),0) as Amount
                FROM  Contract
                WHERE Contract =  @Contract";

            InitCommand(sql, CommandType.Text);

            AddParameter("Contract", code);


            try
            {
                object obj = executeScalar();
                if (obj == DBNull.Value) ret = 0;
                else ret = Convert.ToDecimal(obj);
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }
            if (sErr != "") ErrorLog.SetLog(sErr);

            return ret;
        }

        public decimal getTotalAmtHV(string code, ref string sErr)
        {
            decimal ret = 0;
            openConnection();
            string sql = @"SELECT isnull(SUM(LimitAmt),0) as Amount
                FROM  Contract1
                WHERE Contract =  @Contract";

            InitCommand(sql, CommandType.Text);

            AddParameter("Contract", code);


            try
            {
                object obj = executeScalar();
                if (obj == DBNull.Value) ret = 0;
                else ret = Convert.ToDecimal(obj);
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }
            if (sErr != "") ErrorLog.SetLog(sErr);

            return ret;
        }

        public string updateTotalHL(string code)
        {
            string sErr = "";
            string sql = @"UPDATE    Contract
	                SET	CummulativeAmt = (SELECT     SUM(Amt) AS Amount
			                FROM          InvoiceRegister
			                WHERE      (Contract = @Contract)), 
		                RemainingAmt = LimitAmt - (SELECT     SUM(Amt) AS Amount
			                FROM          InvoiceRegister
			                WHERE      (Contract = @Contract))
                WHERE     (Contract = @Contract)";
            openConnection();

            InitCommand(sql, CommandType.Text);
            AddParameter("Contract", code);


            try
            {
                executeNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }

            return sErr;
        }

        public string updateTotalHV(string code)
        {
            string sErr = "";
            string sql = @"UPDATE    Contract1
	                SET	CummulativeAmt = (SELECT     SUM(Amt) AS Amount
			                FROM          InvoiceRegister1
			                WHERE      (Contract = @Contract)), 
		                RemainingAmt = LimitAmt - (SELECT     SUM(Amt) AS Amount
			                FROM          InvoiceRegister1
			                WHERE      (Contract = @Contract))
                WHERE     (Contract = @Contract)";
            openConnection();

            InitCommand(sql, CommandType.Text);
            AddParameter("Contract", code);


            try
            {
                executeNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }

            return sErr;
        }

        public DataTable getTotalAmtsHL(string code, ref string sErr)
        {
            InitConnect();
            string sql = @"SELECT isnull(SUM(LimitAmt),0) as LimitAmt, isnull(SUM(RemainingAmt),0) as RemainingAmt
                FROM  Contract
                WHERE Contract =  @Contract";

            InitCommand(sql, CommandType.Text);

            AddParameter("Contract", code);

            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            if (sErr != "") ErrorLog.SetLog(sErr);

            return list;
        }

        public DataTable getTotalAmtsHV(string code, ref string sErr)
        {
            InitConnect();
            string sql = @"SELECT isnull(SUM(LimitAmt),0) as LimitAmt, isnull(SUM(RemainingAmt),0) as RemainingAmt
                FROM  Contract1
                WHERE Contract =  @Contract";

            InitCommand(sql, CommandType.Text);

            AddParameter("Contract", code);

            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            if (sErr != "") ErrorLog.SetLog(sErr);

            return list;
        }

        string category = "T9";
        public int insertToSunSytem(string db, ContractInfo info, ref string sErr, string connectString = "")
        {
            int ret = 0;
            string sql = @"INSERT INTO SSRFANV([SUN_DB],[CATEGORY],[CODE],[LOOKUP],[UPDATED],[NAME],[PROHB_POST],[BUDGET_CHECK],[BUDGET_STOP],[DATA_1])
                VALUES(@SUN_DB,@CATEGORY,@CODE,'',@UPDATED,@NAME,'','','','')";

            if (connectString == "") connectString = ConnectionString;

            SqlConnection conn = new SqlConnection(connectString);
            SqlCommand command = new SqlCommand(sql, conn);
            SqlParameter p = command.CreateParameter(); p.ParameterName = "SUN_DB"; p.Value = db;
            command.Parameters.Add(p);
            int len = Math.Min(info.Contract.Length, 15);
            p = command.CreateParameter(); p.ParameterName = "CODE"; p.Value = info.Contract.Substring(0, len);
            command.Parameters.Add(p);
            len = Math.Min(info.Desc.Length, 30);
            p = command.CreateParameter(); p.ParameterName = "NAME"; p.Value = info.Desc.Substring(0, len);
            command.Parameters.Add(p);
            p = command.CreateParameter(); p.ParameterName = "UPDATED"; p.Value = Convert.ToInt32(DateTime.Today.ToString("yyyyMMdd"));
            command.Parameters.Add(p);
            p = command.CreateParameter(); p.ParameterName = "CATEGORY"; p.Value = category;
            command.Parameters.Add(p);
            conn.Open();
            try
            {
                object o = command.ExecuteScalar();
                if (o != DBNull.Value) ret = Convert.ToInt32(ret);
            }
            catch (Exception ex) { sErr = ex.Message; }
            finally { conn.Close(); }
            return ret;
        }

        public bool existsInSunSytem(string db, ContractInfo info, ref string sErr, string connectString = null)
        {
            string sql = @"SELECT TOP 1 SUN_DB,CODE FROM SSRFANV
                WHERE SUN_DB = @SUN_DB AND CATEGORY = @CATEGORY AND CODE = @CODE ";

            if (connectString == null) connectString = ConnectionString;

            SqlConnection conn = new SqlConnection(connectString);
            SqlCommand command = new SqlCommand(sql, conn);
            SqlParameter p = command.CreateParameter(); p.ParameterName = "SUN_DB"; p.Value = db;
            command.Parameters.Add(p);
            p = command.CreateParameter(); p.ParameterName = "CODE"; p.Value = info.Contract;
            command.Parameters.Add(p);
            p = command.CreateParameter(); p.ParameterName = "CATEGORY"; p.Value = category;
            command.Parameters.Add(p);
            SqlDataAdapter adapter = new SqlDataAdapter(command);
            DataTable dt = new DataTable();
            try
            {
                adapter.Fill(dt);
            }
            catch (Exception ex) { sErr = ex.Message; }
            return dt.Rows.Count > 0;
        }
    }
}
